import openpyxl
import sys
from pathlib import Path
import os
from datetime import datetime
from openpyxl.styles import Alignment
import xlrd
import xlwt
from collections import Counter
# workbook = xlrd.open_workbook('./test/B淋巴细胞(CD3-CD19+).xls')
# sheet = workbook.sheet_by_index(0)

# data = [sheet.row_values(row) for row in range(sheet.nrows)]



def increment_column(column:str)->str:
      # Convert column letter to number
    column_num = 0
    for i, char in enumerate(reversed(column)):
        column_num += (ord(char) - ord('A') + 1) * (26 ** i)

    # Increment the column number
    column_num += 1

    # Convert back to column letter
    result = ""
    while column_num > 0:
        remainder = (column_num - 1) % 26
        result = chr(ord('A') + remainder) + result
        column_num = (column_num - 1) // 26

    return result

class initial_process:
    def __init__(self, project_name: str, file_path: str):
        self.file_path = file_path
        self.project_name = project_name

    

    def validate_format(self):
        data = self.read_excel(start=0)
       
        if data[0][1] != '患者编码' :
    
            stander_header = ['患者类型', '患者编码', '患者姓名', '患者性别', '患者年龄', '科室', '病区', '床号', '检验日期', '检验仪器', '标本号', '项目编码', '项目名称', '结果', '单位', '参考范围', '标本类型', '诊断', '审核者', '检验备注', '条码号', '后处理架子号', '开立医生', '患者出生日期', '患者身份证号', '患者电话', '居住地址', '采集时间', '审核时间']

            print(f"文件 {file_path} 检测到格式错误，表格第一行应为{','.join(stander_header)}")
            sys.exit()
        return self
            

    def get_data(self)->list:
        pass

    def read_excel(self,start:int)->list:
        pass


class procecess_xlsx(initial_process):
    def __init__(self, project_name, file_path):
        super().__init__(project_name, file_path)

    def read_excel(self,start) -> list:
        processing_work_book = openpyxl.load_workbook(self.file_path)
        data = []
        existing_sheet = processing_work_book.active
        for row in existing_sheet.iter_rows(min_row=start, values_only=True):
            data.append(row)
        return data
    def get_data(self)->list:
        data = self.read_excel(start=2)
        return data


class procecess_xls(initial_process):
    def __init__(self, project_name, file_path):
        super().__init__(project_name, file_path)
    
    def get_data(self):
        data = self.read_excel(start=1)
        return data
    
    def read_excel(self,start:int)->list:
        # Open the Excel workbook
        workbook = xlrd.open_workbook(self.file_path)

        # Select the first sheet (you can change the index if your data is in a different sheet)
        sheet = workbook.sheet_by_index(0)

        # Get the number of rows and columns
        num_rows = sheet.nrows
        num_cols = sheet.ncols

        # Iterate through rows and columns to read data
        data = []
        for row_index in range(start,num_rows):
            row_data = []
            for col_index in range(num_cols):
                cell_value = sheet.cell_value(row_index, col_index)
                row_data.append(cell_value)
            data.append(row_data)

        return data


    


class header_map:
    def __init__(self,sorted_project_names:list) -> None:

        self.source_project_name_column_letter = {"letter": "M", "index": 12}

        self.source_dialog_result_column_letter = {"letter": "R", "index": 17}

        self.source_result_column_letter = {"letter": "N", "index": 13}

        self.source_unit_column_letter = {"letter": "O", "index": 14}

        self.source_patient_code_column_letter = {"letter": "B", "index": 1}

        self.source_patient_name_column_letter = {"letter": "C", "index": 2}

        self.source_patient_sex_column_letter = {"letter": "D", "index": 3}

        self.source_patient_age_column_letter = {"letter": "E", "index": 4}

        self.headers = [
            {
                "source_letter": "B",
                "width": 10,
                "col_name": "患者编码",
                "source_index": 1,
                "result_index": 0,
                "result_letter": "A",
            },
            {
                "source_letter": "C",
                "width": 10,
                "col_name": "患者姓名",
                "source_index": 2,
                "result_index": 1,
                "result_letter": "B",
            },
            {
                "source_letter": "D",
                "width": 10,
                "col_name": "患者性别",
                "source_index": 3,
                "result_index": 2,
                "result_letter": "C",
            },
            {
                "source_letter": "E",
                "width": 10,
                "col_name": "患者年龄",
                "source_index": 4,
                "result_index": 3,
                "result_letter": "D",
            },
        ]

        self.map = []

        self.generate_project_map(sorted_project_names=sorted_project_names)

        ## append dialog header
        self.append_map(
            col_name='诊断',
            width=50,
            source_index=self.source_dialog_result_column_letter["index"],
            source_letter=self.source_dialog_result_column_letter["letter"]
            )
        
        self.last_time_max_index = 0

        self.dialog_index = len(self.map) - 1

        self.letter_map = self.map

        self.result_map = {}

        self.map_len = len(self.map)
        
        for index,m in enumerate(self.map):
            
            if index > 3:
                self.result_map[index] = self.source_result_column_letter['index']
            ## patient infomation
            else:
                self.result_map[index] = m['source_index']

            #dialog index
            if index == self.map_len -1:
                self.result_map[index] = self.source_dialog_result_column_letter['index']

    def get_last_time_max_index(self)->int:
        return self.last_time_max_index
    def get_dialog_index(self)->int:
        return self.dialog_index
    
    def update_last_time_max_index(self):
        self.last_time_max_index = len(self.map) - 1

    def generate_project_map(self, sorted_project_names:list)->list:
        self.map = self.headers
        for index, project_name in enumerate(sorted_project_names):
            self.append_project(project_name)
            # sort by index asc
            self.map = sorted(self.map, key=lambda x: x["source_index"])
        
    
    def append_map(self,col_name:str,width:int,source_index:int,source_letter:str):
        current_max_index = self.map[-1]["result_index"]
        current_max_letter = self.map[-1]["result_letter"]
        _i = current_max_index + 1
        _letter = increment_column(current_max_letter)
        self.map.append(
            {
                "col_name": col_name,
                "width": width,
                "result_index": _i,
                "source_index": source_index,
                "result_letter": _letter,
                "source_letter": source_letter,
            }
        )
    def append_project(self,col_name:str):
        self.append_map(
            col_name=col_name,
            width=30,
            source_index=self.source_project_name_column_letter["index"],
            source_letter=self.source_project_name_column_letter["letter"],
            )
        
    def append_project_and_update_result_map(self,col_name:str):
        self.append_map(
            col_name=col_name,
            width=30,
            source_index=self.source_project_name_column_letter["index"],
            source_letter=self.source_project_name_column_letter["letter"],
            )
        self.map_len = len(self.map)
        self.result_map[self.map_len - 1 ] = self.source_result_column_letter['index']
    
    
    def get_map(self):
        return self.map
    
    def get_result_map(self):
        return self.result_map
    
    def get_map_len(self):
        return self.map_len



class item:
    def __init__(
        self, row: list, project_name: str, map: header_map,
        cur_index:int,
        duplicate_times_map:dict,
        project_names :list
    ) -> None:

        self.project_name = project_name
        
        self.row = row

        self.key = None

        self.data = None

        self.header_map = map

        self.duplicate_times_map = duplicate_times_map

        self.cur_index = cur_index

        self.has_set_result = False

        self.time_set = False

        self.project_name_index = 0

        for index,p in enumerate(project_names):
            if p == project_name:
                self.project_name_index = index + 3 + 1
       
        self.assign_key_data()


    def assign_key_data(self):
        
        data = {}

        self.key = self.row[1]

        times = self.duplicate_times_map[self.cur_index]
        
        for index,m in enumerate(self.header_map.get_map()):
            if (m['col_name'] == self.project_name and index > 3 and times == 0 and not self.has_set_result) or index == self.header_map.get_dialog_index():
                data[m['result_index']] = self.row[self.header_map.result_map[m['result_index']]]
                self.has_set_result = True
            if times > 0 and m['col_name'] == self.project_name and index > 3 and not self.time_set: 
                
                saved_result = self.row[self.header_map.result_map[m['result_index']]]
                data[self.project_name_index] = None
                times_index = self.header_map.get_last_time_max_index() + times
                data[times_index] = saved_result
                self.time_set = True
            
            if index <= 3:
                data[m['result_index']] = self.row[m['source_index']]
        
            
        ##find value in row by source index
        self.data = data
    
    def get_key(self):
        return self.key
    
    def get_data(self):
        return self.data
    
class merged_result:

    def __init__(self) -> None:
        self.classified_data = {}

    def put(self, item: item):

        key = item.get_key()

        data = item.get_data()
        if key in self.classified_data:
            existing_data = self.classified_data[key]
            for dkey in data:
                try:
                    if existing_data[dkey] is None or len(existing_data[dkey]) == 0:
                        existing_data[dkey] = data[dkey].strip()
                    else:
                        # try to merge data
                        if data[dkey] is not None:
                            
                            new_val = data[dkey].strip() 
                            if len(new_val) > 0:
                                old_val:str = existing_data[dkey]
                                sp_old_val = old_val.split(';')
                                if new_val not in sp_old_val:
                                    sp_old_val.append(new_val)
                                    existing_data[dkey] = ';'.join(sp_old_val)
                    
                except KeyError as e:
                    # print(f"Caught KeyError : key = {dkey}, data= {data}")
                    existing_data[dkey] = data[dkey]
                    
            self.classified_data[key] = existing_data
        else:
            self.classified_data[key] = data
    
    def get_classfied_data(self):
        return self.classified_data


def add_duplicate_times(arr)->dict:
    # Use Counter to count occurrences of each "id"
    id_counts = Counter()
    counter = {}
    for index,item in enumerate(arr):
        id_value = item[1]
        counter[index] = id_counts[id_value]
        id_counts[id_value] += 1
    
    return counter

arguments = sys.argv[1:]

saving_result_dictory = ""
current_datetime = datetime.now()
formatted_datetime = current_datetime.strftime("%Y-%m-%d_%H-%M-%S")
ext_dir = f"merged_result_{formatted_datetime}"

args_length = len(sys.argv) - 1

fail_files = []
if args_length == 0:
    print("用法: command [directory name]\nExample: main.exe example")
    sys.exit()
directory_path = arguments[0]

if not (os.path.exists(directory_path) and os.path.isdir(directory_path)):
    print(f"The directory '{directory_path}' does not exist or is not a directory.")
    sys.exit()

result = merged_result()

dirs = os.listdir(directory_path)



sorted_dirs = []
project_names = []

for filename in dirs:
    file_path = os.path.join(directory_path, filename)
    project_name = os.path.splitext(os.path.basename(file_path))[0]
    project_names.append(project_name)

sorted_project_names = sorted(project_names)

_map = header_map(sorted_project_names=sorted_project_names)

for filename in dirs:
    file_path = os.path.join(directory_path, filename)
    project_name = os.path.splitext(os.path.basename(file_path))[0]



    if filename.endswith(".xlsx"):
        processor = procecess_xlsx(project_name=project_name,file_path=file_path)
    else:
        processor = procecess_xls(project_name=project_name,file_path=file_path)
    data = processor.validate_format().get_data()

    duplicate_times_map = add_duplicate_times(data)

    max_duplicate_times = max(duplicate_times_map.values())
    

    _map.update_last_time_max_index()
    
    for i in range(0,max_duplicate_times):
        _map.append_project_and_update_result_map(project_name)
    
    #get max duplicated times of current project
    #set a flag to mark duplicated times of each row like 
    
    for i,t in enumerate(data):
        _item = item(row=t,
                        project_name=project_name,
                        map=_map,
                        cur_index=i,
                        duplicate_times_map=duplicate_times_map,
                        project_names = sorted_project_names)

        result.put(item=_item)
       



workbook = openpyxl.Workbook()
sheet = workbook.active
start_cell = "A"
for index, col in enumerate(_map.get_map()):
    sheet["{}{}".format(start_cell, "1")] = col["col_name"]
    sheet.column_dimensions[start_cell].width = col["width"]
    start_cell = increment_column(start_cell)

_map_map = _map.get_map()
start_row = 2
classified_data = result.get_classfied_data()

# print(_map.get_result_map())
# for i in _map.get_map():
#     print(i)
# for lk,i in enumerate(classified_data):
#     print(classified_data[i])

# print(_map.get_result_map())


# sys.exit()
all_letter = []
for k,_n in enumerate(classified_data):
    row = classified_data[_n]
    for i in row:
       
        val = row[i]
        kk = _map_map[i]
        letter = _map_map[i]['result_letter']
        
        sheet["{}{}".format(letter,start_row)] = val
        if letter not in all_letter:
            all_letter.append(letter)
      
    start_row = start_row + 1
for letter in all_letter:
  for ce in sheet[letter]:
            ce.alignment = Alignment(wrap_text=True)
save_path = "{}.xlsx".format(ext_dir)
workbook.save(save_path)

if len(fail_files) == 0:
    print("处理失败文件：\n全部成功!")
else:
    print("处理失败文件：\n".format("\n".join(fail_files)))
print("\n")
print("项目列表：\n{}".format("\n".join(project_names)))
print("\n")
print("处理成功，结果保存在:\n{}".format(save_path))

